﻿// Parts Copyright 2008 Newgrove Consultants Limited
// Modified By John Diss 
// From original work:
//
// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

// THIS CODE HAS BEEN MODIFIED FROM SHARPMAP PROJECT
// Copyright 2005, 2006 - Morten Nielsen (www.iter.dk)
// SharpMap is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
using MsSqlSpatial;
using SharpMap.Data;
using SharpMap.Data.Providers.ShapeFile;
using SharpMap.Expressions;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void ImportFromShapefile(
        SqlString shapefile,
        [DefaultValue("")] SqlString schema_name,
        [DefaultValue("")] SqlString table_name,
        [DefaultValue("")] SqlString column_name,
        [DefaultValue(-1)] SqlInt32 srid,
        [DefaultValue("GEOMETRY")] SqlString type,
        [DefaultValue(false)] SqlBoolean appendRows
        )
    {
        long importedRows;
        TimeSpan elapsedTime;

        // TODO: Impersonation implementation pending. I'm getting the following error:
        // Msg 10312, Level 16, State 49, Procedure ImportFromShapefile, Line 0
        // .NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

        //		WindowsImpersonationContext originalContext = null;
        //		WindowsIdentity callerIdentity = SqlContext.WindowsIdentity;
        //		
        //		if (callerIdentity != null) 
        //		{
        //			originalContext = callerIdentity.Impersonate();
        ImportFromShapefileMain(shapefile.Value, schema_name.Value, table_name.Value, column_name.Value, srid.Value,
                                type.Value, "context connection=true", appendRows.IsTrue, out importedRows,
                                out elapsedTime);
        //		}
        //		if (originalContext != null)
        //			originalContext.Undo();

        SqlContext.Pipe.Send(string.Format("{0} records imported in {1}.", importedRows, FormatElapsedTime(elapsedTime)));
    }

    public static string FormatElapsedTime(TimeSpan elapsedTime)
    {
        if (elapsedTime.Minutes < 1)
            return string.Format("{0} seconds", elapsedTime.TotalSeconds);
        else if (elapsedTime.Hours < 1)
            return string.Format("{0} minutes", elapsedTime.TotalMinutes.ToString("N"));
        else
            return elapsedTime.ToString();
    }

    public static void ImportFromShapefileMain(
        string shapefile,
        string schema_name,
        string table_name,
        string column_name,
        int srid,
        string type,
        string connectionString,
        bool appendRows,
        out long importedRows,
        out TimeSpan elapsedTime
        )
    {
        // Default table name to shapefile name.
        string filename = Path.GetFileName(shapefile);
        if (string.IsNullOrEmpty(table_name))
            table_name = filename.Remove(filename.Length - 4, 4).Replace(" ", ""); // remove .shp

        if (string.IsNullOrEmpty(column_name))
            column_name = "WKB_Geometry";

        using (IDataImportProcess process = new ShapefileImportProcess(shapefile))
        {
            DataImportProcess(process, schema_name, table_name, column_name, srid, type, connectionString, appendRows,
                              out importedRows, out elapsedTime);
        }
    }

    public static void DataImportProcess(
        IDataImportProcess process,
        string schemaName,
        string tableName,
        string columnName,
        int srid,
        string type,
        string connectionString,
        bool appendRows,
        out long importedRows,
        out TimeSpan elapsedTime
        )
    {
        importedRows = -1;
        elapsedTime = TimeSpan.Zero;

        DateTime start = DateTime.Now;

        DatabaseHelper.ContextConnectionString = connectionString;

        if (!SpatialRefSysTable.IsValidSRID(srid))
            throw new InvalidSridException(srid);

        using (SqlConnection connection = DatabaseHelper.ContextConnection)
        {
            connection.Open();
            using (SqlTransaction transaction = connection.BeginTransaction())
            using (SqlCommand command = connection.CreateCommand())
            {
                command.Transaction = transaction;
                command.CommandTimeout = 0;
                ExecuteCreateMetaTables(command);
                schemaName = DatabaseHelper.CheckSchemaName(command, schemaName);
                Dictionary<string, Type> columns = process.GetColumns();

                if (appendRows)
                {
                    GeometryTable.ExecuteDropIndexes(command, schemaName, tableName, columnName);
                    GeometryTable.ExecuteDropConstraintSRID(command, schemaName, tableName, columnName);
                    GeometryTable.ExecuteDropConstraintGeometryType(command, schemaName, tableName, columnName);
                    GeometryTable.ExecuteDropTrigger(command, schemaName, tableName, columnName);
                }
                else
                {
                    //Try to drop table if it exists
                    try
                    {
                        DatabaseHelper.ExecuteDropTable(command, schemaName, tableName);
                    }
                    catch
                    {
                    }

                    //Create new table for storing the datasource
                    string createColumns = "";
                    foreach (var col in columns)
                    {
                        if (col.Value != typeof (String))
                            createColumns += ", " + col.Key + " " + Type2SqlType(col.Value);
                        else
                            createColumns += ", " + col.Key + " VARCHAR(4000)";
                    }
                    GeometryTable.ExecuteCreate(command, schemaName, tableName, columnName, createColumns);
                }

                CreateInsertCommand(command, schemaName, tableName, columnName, columns);
                process.Execute(command, columnName, srid, out importedRows);
                command.Parameters.Clear();

                // Create indexes, meta-info and constraints
                if (!appendRows)
                {
                    GeometryColumnsTable.ExecuteAddMetaRecord(command, schemaName, tableName, columnName, srid, type);
                    ExecuteAddGeometryQueryFunctions(command, schemaName, tableName, columnName);
                }
                GeometryTable.ExecuteAddIndexes(command, schemaName, tableName, columnName);
                GeometryTable.ExecuteAddConstraintSRID(command, schemaName, tableName, columnName, srid);
                GeometryTable.ExecuteAddConstraintGeometryType(command, schemaName, tableName, columnName, type);
                GeometryTable.ExecuteCreateTrigger(command, schemaName, tableName, columnName);

                transaction.Commit();
            }
        }

        elapsedTime = DateTime.Now - start;
    }

    private static string FixOidColumnName(string columnName)
    {
        if (string.Compare("oid", columnName, true) == 0)
            return columnName + "_shp";
        return columnName;
    }

    private static void CreateInsertCommand(SqlCommand command, string schemaName, string tableName,
                                            string geometryColumnName, Dictionary<string, Type> columns)
    {
        //Create insert script
        string strSQL = " (";
        foreach (var col in columns)
            strSQL += "@" + col.Key + ",";

        strSQL += "@" + geometryColumnName + ")";
        strSQL = "INSERT INTO [" + schemaName + "].[" + tableName + "] " + strSQL.Replace("@", "") + " VALUES" + strSQL;

        DatabaseHelper.BuildCommand(command, strSQL);
        //Add feature columns parameters
        foreach (var col in columns)
        {
            if (col.Value == typeof (string))
                command.Parameters.Add("@" + col.Key, SqlDbType.VarChar, 4000);
            else
                command.Parameters.Add("@" + col.Key, Type2SqlType(col.Value));
        }
        //Add geometry column parameter
        command.Parameters.Add("@" + geometryColumnName, SqlDbType.VarBinary, int.MaxValue);

        command.Prepare();
    }

    /// <summary>
    /// Returns the name of the SqlServer datatype based on a .NET datatype
    /// </summary>
    /// <param name="t"></param>
    /// <returns></returns>
    private static SqlDbType Type2SqlType(Type t)
    {
        switch (t.ToString())
        {
            case "System.Boolean":
                return SqlDbType.Bit;
            case "System.Single":
                return SqlDbType.Real;
            case "System.Double":
                return SqlDbType.Float;
            case "System.UInt16":
            case "System.Int16":
                return SqlDbType.SmallInt;
            case "System.UInt32":
            case "System.Int32":
                return SqlDbType.Int;
            case "System.UInt64":
            case "System.Int64":
                return SqlDbType.BigInt;
            case "System.DateTime":
                return SqlDbType.DateTime;
            case "System.Byte[]":
                return SqlDbType.Image;
            case "System.String":
                return SqlDbType.VarChar;
            default:
                throw (new NotSupportedException("Unsupported datatype '" + t.Name + "' found in datasource"));
        }
    }

    #region Nested type: IDataImportProcess

    public interface IDataImportProcess : IDisposable
    {
        event EventHandler<ImportEventArgs> ItemImported;

        Dictionary<string, Type> GetColumns();
        void Execute(SqlCommand command, string columnName, int srid, out long importedRows);
    }

    #endregion

    #region Nested type: ImportEventArgs

    public class ImportEventArgs
        : EventArgs
    {
        private readonly long _record;
        private readonly long _total;

        public ImportEventArgs(long record, long total)
        {
            _record = record;
            _total = total;
        }

        public long TotalItemCount
        {
            get { return _total; }
        }

        public long ItemNumber
        {
            get { return _record; }
        }
    }

    #endregion

    #region Nested type: ShapefileImportProcess

    public class ShapefileImportProcess
        : IDisposable, IDataImportProcess
    {
        private readonly ShapeFileProvider _provider;
        private bool disposed;


        public ShapefileImportProcess(string filename)
        {
            _provider = new ShapeFileProvider(filename, Global.Services.GeometryFactory,
                                              Global.Services.CoordinateSystemFactory);
            _provider.Open();
        }

        public DataTable DbfSchema
        {
            get { return _provider.CreateNewTable(); }
        }

        #region IDataImportProcess Members

        public void Execute(SqlCommand command, string columnName, int srid, out long importedRows)
        {
            if (!_provider.IsOpen)
                _provider.Open();

            importedRows = 0;
            int total = _provider.GetFeatureCount();

            foreach (
                IFeatureDataRecord v in
                    _provider.ExecuteFeatureQuery(new FeatureQueryExpression(_provider.GetExtents(),
                                                                             SpatialOperation.Intersects)))
            {
                Dictionary<string, Type> cols = GetColumns();

                int i = 0;
                foreach (string column in cols.Keys)
                {
                    command.Parameters["@" + column].Value = v.GetValue(i);
                    i++;
                }
                command.Parameters["@" + columnName].Value = ConvertGeometry.ToSqlBytes(v.Geometry, srid).Value;

                command.ExecuteNonQuery();
                importedRows++;

                if (ItemImported != null)
                    ItemImported(this, new ImportEventArgs(importedRows, total));
            }
        }

        public event EventHandler<ImportEventArgs> ItemImported;

        public Dictionary<string, Type> GetColumns()
        {
            var d = new Dictionary<string, Type>();

            foreach (DataColumn a in DbfSchema.Columns)
                d.Add(FixOidColumnName(a.ColumnName), a.DataType);

            return d;
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        #endregion

        ~ShapefileImportProcess()
        {
            Dispose(false);
        }

        private void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (_provider.IsOpen)
                    _provider.Close();
            }
            disposed = true;
        }
    }

    #endregion
} ;